package com.kk.base.dao.home;

import com.kk.base.entity.home.ExpressMailOrder;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
public interface ExpressMailOrderDao extends JpaRepository<ExpressMailOrder,Long>, JpaSpecificationExecutor<ExpressMailOrder> {

    @Query("select emo from ExpressMailOrder emo where emo.id = :id")
    ExpressMailOrder find(@Param("id")Long id);

    /**
     * 根据状态获取总数
     * @param orderStatus
     * @return
     */
    Long countByOrderStatus(Integer orderStatus);


    /**
     * 查询今年的各月份的签收订单
     * @return
     */
    @Query(value = "SELECT MONTH\n" +
            "\t( create_time ) month,\n" +
            "\tcount( id ) count \n" +
            "FROM\n" +
            "\texpress_mail_order \n" +
            "WHERE\n" +
            "\torder_status = 6\n" +
            "\tAND YEAR ( create_time ) = YEAR( now( ) ) \n" +
            "GROUP BY\n" +
            "\tMONTH ( create_time )",nativeQuery = true)
    List<Object> orderCountAndMonthByNowYear();


    ExpressMailOrder findByWaybillNumber(String waybillNumber);

}
